﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
namespace BBMS.DLL.Base
{
    public class ObjectBase
    {
        protected string strHaveExistedRecordMsg = "";
        public virtual string[] KeyFields { get { return null; } }
        public virtual string[] IDFields { get { return null; } }
        public virtual string TableName { get { return null; } }
        public virtual object Key { get { return null; } 
            // Key have not "set": Toan M. Ha
            /*set { Key = value; } */
        }
        public virtual string Name  { get { return null; } }
        public virtual string ExWhereClause
        {
            get
            {
                return "";
            }
        }
        public virtual string ExistedObjectString{
            get
            {
                return "Lỗi: Đã tồn tại đối tượng trong cơ sở dữ liệu";
            }
        }
        public virtual ArrayList GetAllObject() {
            ArrayList arRet = null;
            try
            {
                MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(Config.ConnectionString());
                con.Open();
                MySql.Data.MySqlClient.MySqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from " + TableName;
                if (this.ExWhereClause != "") cmd.CommandText += " where " + this.ExWhereClause;
                MySql.Data.MySqlClient.MySqlDataReader rd = cmd.ExecuteReader();
                arRet = new ArrayList();
                while (rd.Read())
                    arRet.Add(DataReaderToObject(rd));
                
                rd.Close();
                rd.Dispose();
                con.Dispose();
                con.Close();
            }
            catch { }
            return arRet;
        }
        public virtual ObjectBase DataReaderToObject(MySql.Data.MySqlClient.MySqlDataReader rd) { return null;}
        public virtual ArrayList GetObjectByPage(out int curPage, out int totalPage, FilterParams fParams) 
        {
            ArrayList arRet = null;
            curPage = 0; 
            totalPage = 0;
            try
            {
                string[] idFiels = IDFields;

                MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(Config.ConnectionString());
                con.Open();
                MySql.Data.MySqlClient.MySqlCommand cmd = con.CreateCommand();

                cmd.CommandType = CommandType.Text;
                string exWhere = "";
                if (ExWhereClause != "") exWhere = ExWhereClause + " and ";
                if (fParams.ArParams != null && fParams.ArParams.Count > 0)
                {
                    cmd.CommandText = string.Format("select count({0}) from {1} where {2} {3}", idFiels[0], TableName, exWhere, fParams.SQLString);
                    cmd.Parameters.Clear();
                    for (int i = 0; i < fParams.ArParams.Count; i++)

                        //bao comment
                     //   cmd.Parameters.AddWithValue("@" + ((FilterCondition)fParams.ArParams[i]).FieldName, ((FilterCondition)fParams.ArParams[i]).Value);
                        cmd.Parameters.AddWithValue("@" + ((FilterCondition)fParams.ArParams[i]).Alias, ((FilterCondition)fParams.ArParams[i]).Value);
                }
                else cmd.CommandText = string.Format("select count({0}) from {1} where {2}(1=1) ", idFiels[0], TableName, exWhere);
 
                totalPage = (int)(((long)cmd.ExecuteScalar() - 1) / Config.RowPerPage) + 1;
                curPage = fParams.CurrentPage;

                if (curPage >= totalPage) curPage = totalPage;
                if (curPage <= 0) curPage = 1;

                if (fParams.ArParams != null && fParams.ArParams.Count > 0)
                {
                    cmd.CommandText = string.Format("select * from {0} where {1} {2}",TableName, exWhere, fParams.SQLString);
                    cmd.Parameters.Clear();
                    for (int i = 0; i < fParams.ArParams.Count; i++)
                        //bao comment
                        //cmd.Parameters.AddWithValue("@" + ((FilterCondition)fParams.ArParams[i]).FieldName, ((FilterCondition)fParams.ArParams[i]).Value);
                        cmd.Parameters.AddWithValue("@" + ((FilterCondition)fParams.ArParams[i]).Alias, ((FilterCondition)fParams.ArParams[i]).Value);
                }
                else cmd.CommandText = string.Format("select * from {0} where {1} (1=1)", TableName,exWhere);
                cmd.CommandText += string.Format(" limit {0},{1} ", (curPage - 1) * Base.Config.RowPerPage, Base.Config.RowPerPage);

                MySql.Data.MySqlClient.MySqlDataReader rd = cmd.ExecuteReader();
                arRet = new ArrayList();
                while (rd.Read())
                    arRet.Add(DataReaderToObject(rd));

                rd.Close();
                rd.Dispose();
                con.Dispose();
                con.Close();
            }
            catch (Exception ex) { }
            return arRet; 
        }
        public virtual ArrayList GetAllObject(FilterParams fParams) {
            ArrayList arRet = null;
            try
            {
                MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(Config.ConnectionString());
                con.Open();
                MySql.Data.MySqlClient.MySqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from " + TableName + " where (1=1)";
                if (this.ExWhereClause != "") cmd.CommandText += " and " + this.ExWhereClause;
                if(fParams.SQLString != "") cmd.CommandText += "and " + fParams.SQLString;
                for (int i = 0; i < fParams.ArParams.Count; i++)
                {
                    cmd.Parameters.AddWithValue(((FilterCondition)fParams.ArParams[i]).Alias,((FilterCondition)fParams.ArParams[i]).Value);
                }
                
                MySql.Data.MySqlClient.MySqlDataReader rd = cmd.ExecuteReader();
                arRet = new ArrayList();
                while (rd.Read())
                    arRet.Add(DataReaderToObject(rd));

                rd.Close();
                rd.Dispose();
                con.Dispose();
                con.Close();
            }
            catch (Exception ex) { }
            return arRet;
        }
       
        public virtual string InsertNew() 
        {
            string msg = "";
            try
            {
                MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(Config.ConnectionString());
                msg = "Lỗi: Không thể mở kết nối tới cơ sở dữ liệu.";
                con.Open();
                msg = "";
                MySql.Data.MySqlClient.MySqlCommand cmd = con.CreateCommand();
                DataRow row = ObjectToDataRow();
                msg = CheckKeyCondition(cmd, row);
                if (msg == "Existed") // ton tai ban ghi trong csdl
                {
                    msg = ExistedObjectString;
                }
                else if (msg == "Error") // loi thao tac csdl
                {
                    msg = "Lỗi: Lỗi thao tác tới cơ sở dữ liệu.";
                }
                else // ok, insert ?
                {
                    msg = "";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Clear();

                    cmd.CommandText = "insert into " + this.TableName + " (";
                    
                    string tmp = "";
                    for (int i = 0; i < row.Table.Columns.Count; i++)
                    {
                        if (i > 0) 
                        { 
                            cmd.CommandText += ",";
                            tmp += ",";
                        }

                        cmd.CommandText += row.Table.Columns[i].ColumnName;
                        tmp += "@" + row.Table.Columns[i].ColumnName;
                        cmd.Parameters.AddWithValue("@" + row.Table.Columns[i].ColumnName, row[i]);
                    }
                    cmd.CommandText += ") values (" + tmp +")";
                    cmd.ExecuteNonQuery();
                    UpdateInsertedID(cmd);
                    InsertLog(cmd, row, Action.Insert);
                }

                con.Close();
                con.Dispose();
            }
            catch (Exception ex)
            {
                msg = "Lỗi: Lỗi thao tác tới cơ sở dữ liệu.";
            }
            return msg;
        }

        public virtual string InsertNew(MySql.Data.MySqlClient.MySqlCommand cmd)
        {
            string msg = "";
            try
            {
                DataRow row = ObjectToDataRow();
                msg = CheckKeyCondition(cmd, row);
                if (msg == "Existed") // ton tai ban ghi trong csdl
                {
                    msg = ExistedObjectString;
                }
                else if (msg == "Error") // loi thao tac csdl
                {
                    msg = "Lỗi: Lỗi thao tác tới cơ sở dữ liệu.";
                }
                else // ok, insert ?
                {
                    msg = "";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Clear();

                    cmd.CommandText = "insert into " + this.TableName + " (";

                    string tmp = "";
                    for (int i = 0; i < row.Table.Columns.Count; i++)
                    {
                        if (i > 0)
                        {
                            cmd.CommandText += ",";
                            tmp += ",";
                        }

                        cmd.CommandText += row.Table.Columns[i].ColumnName;
                        tmp += "@" + row.Table.Columns[i].ColumnName;
                        cmd.Parameters.Add("@" + row.Table.Columns[i].ColumnName, row[i]);
                    }
                    cmd.CommandText += ") values (" + tmp + ")";
                    cmd.ExecuteNonQuery();
                    UpdateInsertedID(cmd);
                    InsertLog(cmd, row, Action.Insert);
                }
            }
            catch (Exception ex)
            {
                msg = "Lỗi: Lỗi thao tác tới cơ sở dữ liệu.";
            }
            return msg;
        }

        public virtual void InsertLog(MySql.Data.MySqlClient.MySqlCommand cmd, DataRow row, Action action)
        {
            try
            {
                string table = this.TableName;
                string userName = Config.CurUser.UserName;
                if (userName == null) userName = "";
                string computerName = System.Net.Dns.GetHostName();
                string computerIP = System.Net.Dns.GetHostByName(computerName).AddressList[0].ToString();
                int logAction = (int)action;
                string message = action.ToString() + " " + table + ".";
                if (row != null)
                {
                   
                    string idList = "";
                    string keyList = "";
                    for (int i = 0; i < this.IDFields.Length; i++)
                    {
                        idList += " IDField : " + this.IDFields[i] + " with value " + row[this.IDFields[i]] + ",";
                    }
                    //idList = idList.Substring(0, idList.Length - 1);

                    for (int i = 0; i < this.KeyFields.Length; i++)
                    {
                        keyList += " KeyField : " + this.KeyFields[i] + " with value " + row[this.KeyFields[i]] + ",";
                    }
                    keyList = keyList.Substring(0, keyList.Length - 1);
                    message += idList + keyList;
                }

                cmd.CommandText = "insert into " + BBMS.FrontEnd.Constant.FixedCode.LOG_TABLE 
                    + " (UserName,Action,LogDate,Message,TableName,ComputerIP) values "
                    + " (@UserName,@Action,NOW(),@Message,@TableName,@ComputerIP) ";
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Parameters.AddWithValue("@Action", logAction);
               // cmd.Parameters.AddWithValueWithValue("@LogDate", "NOW()");
                cmd.Parameters.AddWithValue("@Message", message);
                cmd.Parameters.AddWithValue("@TableName", table);
                cmd.Parameters.AddWithValue("@ComputerIP", computerIP);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
            
        }

        public virtual void UpdateInsertedID(MySql.Data.MySqlClient.MySqlCommand cmd)
        {
            //cmd.CommandText = "SELECT LAST_INSERT_ID()";
            //this.Key = (long)cmd.ExecuteScalar();
        }

        public virtual string Save() {
            string msg = "";
            try
            {
                MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(Config.ConnectionString());
                msg = "Lỗi: Không thể mở kết nối tới cơ sở dữ liệu.";
                con.Open();
                msg = "";
                MySql.Data.MySqlClient.MySqlCommand cmd = con.CreateCommand();
                DataRow row = ObjectToDataRow();
                
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + this.TableName + " set ";

                string[] keyFiels = KeyFields;
                string[] idFiels = IDFields;
                bool addPhay = false;
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    
                    bool add=true;
                    if (keyFiels != null)
                    {
                        foreach (string str in keyFiels)
                        {
                            if (str.CompareTo(row.Table.Columns[i].ColumnName) == 0)
                            {
                                add = false;
                                break;
                            }
                        }
                    }
                    if (add)
                    {
                        foreach (string str in idFiels)
                        {
                            if (str.CompareTo(row.Table.Columns[i].ColumnName) == 0)
                            {
                                add = false;
                                break;
                            }
                        }
                    }
                    if (add)
                    {
                        if(addPhay) cmd.CommandText += ",";
                        if (!addPhay) addPhay = true;
                        
                        // check for: do not update key or id fields
                        cmd.CommandText += row.Table.Columns[i].ColumnName + "=@" + row.Table.Columns[i].ColumnName;
                        cmd.Parameters.AddWithValue("@" + row.Table.Columns[i].ColumnName, row[i]);
                    }
                }
                cmd.CommandText += " where ";
                addPhay = false;
                foreach (string str in idFiels)
                {
                    if(addPhay) cmd.CommandText += ",";
                    if (!addPhay) addPhay = true;
                    cmd.CommandText += string.Format("{0}=@{0}", str);
                    cmd.Parameters.AddWithValue("@" + str, row[str]);
                }

                cmd.ExecuteNonQuery();
                InsertLog(cmd, row, Action.Update);
                con.Close();
                con.Dispose();
            }
            catch
            {
                msg = "Lỗi: Lỗi thao tác tới cơ sở dữ liệu.";
            }
            return msg;
        }

        public virtual string Save(MySql.Data.MySqlClient.MySqlCommand cmd)
        {
            string msg = "";
            try
            {
                DataRow row = ObjectToDataRow();

                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + this.TableName + " set ";

                string[] keyFiels = KeyFields;
                string[] idFiels = IDFields;
                bool addPhay = false;
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {

                    bool add = true;
                    if (keyFiels != null)
                    {
                        foreach (string str in keyFiels)
                        {
                            if (str.CompareTo(row.Table.Columns[i].ColumnName) == 0)
                            {
                                add = false;
                                break;
                            }
                        }
                    }
                    if (add)
                    {
                        foreach (string str in idFiels)
                        {
                            if (str.CompareTo(row.Table.Columns[i].ColumnName) == 0)
                            {
                                add = false;
                                break;
                            }
                        }
                    }
                    if (add)
                    {
                        if (addPhay) cmd.CommandText += ",";
                        if (!addPhay) addPhay = true;

                        // check for: do not update key or id fields
                        cmd.CommandText += row.Table.Columns[i].ColumnName + "=@" + row.Table.Columns[i].ColumnName;
                        cmd.Parameters.Add("@" + row.Table.Columns[i].ColumnName, row[i]);
                    }
                }
                cmd.CommandText += " where ";
                addPhay = false;
                foreach (string str in idFiels)
                {
                    if (addPhay) cmd.CommandText += ",";
                    if (!addPhay) addPhay = true;
                    cmd.CommandText += string.Format("{0}=@{0}", str);
                    cmd.Parameters.Add("@" + str, row[str]);
                }

                cmd.ExecuteNonQuery();
                InsertLog(cmd, row, Action.Update);
            }
            catch
            {
                msg = "Lỗi: Lỗi thao tác tới cơ sở dữ liệu.";
            }
            return msg;
        }

        public virtual string Delete() 
        {
            string msg = "";
            try
            {
                MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(Config.ConnectionString());
                msg = "Lỗi: Không thể mở kết nối tới cơ sở dữ liệu.";
                con.Open();
                
                msg = "";
                MySql.Data.MySqlClient.MySqlCommand cmd = con.CreateCommand();
                DataRow row = ObjectToDataRow();

                string[] idFiels = this.IDFields;
                if (idFiels != null && idFiels.Length > 0)
                {
                    if (CanDelete(cmd, row))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Clear();
                        cmd.CommandText = "delete from " + TableName + " where ";

                        for (int i = 0; i < idFiels.Length; i++)
                        {
                            if (i > 0) cmd.CommandText += " and ";
                            cmd.CommandText += string.Format(" {0}=@{0} ", idFiels[i]);
                            cmd.Parameters.AddWithValue("@" + idFiels[i], row[idFiels[i]]);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    else msg = "Lỗi: Không thể xóa bản ghi do tính toàn vẹn dữ liệu.";
                }
                InsertLog(cmd, row, Action.Delete);
                con.Dispose();
                con.Close();
            }
            catch
            {
                msg = "Lỗi: Lỗi thao tác tới cơ sở dữ liệu.";
            }
            return msg;
        }

        /*Must be override in inherited object*/
        public virtual bool CanDelete(MySql.Data.MySqlClient.MySqlCommand cmd, DataRow row)
        {
            return false;
        }

        public virtual string CheckKeyCondition(MySql.Data.MySqlClient.MySqlCommand cmd,DataRow row) {
            string retStr = "";
            try
            {
                string[] keyFiels = this.KeyFields;
                if (keyFiels != null && keyFiels.Length > 0)
                {
                    cmd.CommandText = string.Format("select count({0}) from {1} where ", keyFiels[0], this.TableName);
                    cmd.Parameters.Clear();
                    for (int i = 0; i < keyFiels.Length; i++)
                    {
                        if (i > 0) cmd.CommandText += " and ";
                        cmd.CommandText += string.Format(" {0}=@{0} ", keyFiels[i]);
                        cmd.Parameters.AddWithValue("@" + keyFiels[i], row[keyFiels[i]]);
                    }
                    cmd.CommandType = CommandType.Text;
                    long count = (long)cmd.ExecuteScalar();
                    if (count > 0)
                    {
                        retStr = "Existed";
                    }
                }
            }
            catch
            {
                retStr = "Error";
            }
            return retStr;
        }

        public virtual DataTable GetObjectDataTable() { return null; }
        public virtual DataRow ObjectToDataRow() { return null; }
        public virtual ObjectBase DataRowToObject(DataRow row) { return null; }
    }
}
